package com.cipe.cmrs.dao;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;

import com.cipe.cmrs.model.view.ProjectPaymentView;

@Repository
public class CipeProjectDao {
    @PersistenceContext
    private EntityManager em;

    public List<ProjectPaymentView> getProjectPaymentView(Integer excludePaymentRequest) {
        StringBuilder queryBuilder = new StringBuilder();
        queryBuilder.append("SELECT ");
        queryBuilder.append("projectpayment.id, ");
        queryBuilder.append("projectpayment.projectId, ");
        queryBuilder.append("projectpayment.title, ");
        queryBuilder.append("projectpayment.costCenterNumber, ");
        queryBuilder.append("projectpayment.projectTypeDesc, ");
        queryBuilder.append("projectpayment.status, ");
        queryBuilder.append("projectpayment.region, ");
        queryBuilder.append("projectpayment.country, ");
        queryBuilder.append("projectpayment.acronym, ");
        queryBuilder.append("projectpayment.projectStartDate, ");
        queryBuilder.append("projectpayment.projectEndDate, ");
        queryBuilder.append("projectpayment.partnerName, ");
        queryBuilder.append("projectpayment.budgetOverride, ");
        queryBuilder.append("projectpayment.statusOverride, ");
        queryBuilder.append("projectpayment.totalFunds, ");
        queryBuilder.append("projectpayment.remainingFunds, ");
        queryBuilder.append("projectpayment.remainingFunds");
        if (excludePaymentRequest != null && excludePaymentRequest > 0) {
            queryBuilder
                .append(" + ISNULL((SELECT pr.totalAmount FROM vw_paymentrequest pr WHERE pr.paymentRequestId = :paymentRequestId AND pr.projectId = projectpayment.id), 0)");
        }
        queryBuilder.append(" FROM ");
        queryBuilder.append(ProjectPaymentView.class.getName() + " projectpayment ");

        Query query = em.createQuery(queryBuilder.toString());
        if (excludePaymentRequest != null && excludePaymentRequest > 0) {
            query.setParameter("paymentRequestId", excludePaymentRequest);
        }
        return convert(query.getResultList());
    }

    @SuppressWarnings("rawtypes")
    private List<ProjectPaymentView> convert(List list) {
        List<ProjectPaymentView> results = new ArrayList<ProjectPaymentView>();
        for (Object obj : list) {
            Object[] arr = (Object[]) obj;
            ProjectPaymentView pr = new ProjectPaymentView();
            pr.setId((Integer) arr[0]);
            pr.setProjectId((String) arr[1]);
            pr.setTitle((String) arr[2]);
            pr.setCostCenterNumber((String) arr[3]);
            pr.setProjectTypeDesc((String) arr[4]);
            pr.setStatus((String) arr[5]);
            pr.setRegion((String) arr[6]);
            pr.setCountry((String) arr[7]);
            pr.setAcronym((String) arr[8]);
            pr.setProjectStartDate((Date) arr[9]);
            pr.setProjectEndDate((Date) arr[10]);
            pr.setPartnerName((String) arr[11]);
            pr.setBudgetOverride((Boolean) arr[12]);
            pr.setStatusOverride((Boolean) arr[13]);
            pr.setTotalFunds((BigDecimal) arr[14]);
            pr.setRemainingFunds((BigDecimal) arr[15]);
            pr.setRemainingFundsPRExcluded((BigDecimal) arr[16]);
            results.add(pr);
        }
        return results;
    }
}
